﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using smp.ButterflyCakes.Models;
using smp.ButterflyCakes.Models.Data;
using smp.ButterflyCakes.Models.FormModels.Order;
using smp.ButterflyCakes.Models.InputModels.Order;
using smp.ButterflyCakes.Models.ViewModels.Order;
using smp.ButterflyCakes.Repository.Databases;
using smp.ButterflyCakes.Repository.Interfaces;
using smp.QueryDb;
using smp.QueryDb.Structure;

namespace smp.ButterflyCakes.Repository.Implementation
{
    public class OrderRepository : IOrderRepository
    {
        private readonly IDbSessionFactory _sessionFactory;

        public OrderRepository()
        {
            _sessionFactory = new DbSessionFactory("ButterflyCakes");
        }

        public OrderRepository(IDbSessionFactory sessionFactory)
        {
            _sessionFactory = sessionFactory;
        }

        #region Implementation of IOrderRepository

        public EditMenuView GetOrderMenuView(Guid orderId, Guid activeStageId)
        {
            List<EditMenuDataItem> data;
            using (var session = _sessionFactory.Create())
            {
                data = session.Query(new Query<EditMenuDataItem>()
                                         .From(Db.EntityStage
                                                   .Join(new Join(Db.Component)
                                                             .On(EntityStage.ComponentId).IsEqualTo(Component.Id)))
                                         .Map(x => x.ComponentTitle).To(Component.Title)
                                         .Map(x => x.StageId).To(EntityStage.Id)
                                         .Map(x => x.StageTitle).To(EntityStage.Title)
                                         .Map(x => x.Stage).To(EntityStage.Stage)
                                         .OrderBy(EntityStage.Index)
                                         .Where(new Filter<EditMenuDataItem>(EntityStage.OrderId).Is.EqualTo(orderId))).Results;
            }
            if (data == null)
            {
                data = new List<EditMenuDataItem>();
            }
            return new EditMenuView(data, activeStageId);
        }

        public Stage GetCurrentStage(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                var result = session.Load(new Query()
                                              .From(Db.Order
                                                        .Join(new Join(Db.EntityStage)
                                                                  .On(Order.CurrentLastStageId).IsEqualTo(EntityStage.Id)))
                                              .Select(EntityStage.Stage)
                                              .Where(new Filter(Order.Id).Is.EqualTo(orderId)));

                var stageData = result.Value("Stage");
                if(stageData == null)
                    throw new DataException("No current stage could be loaded.");

                int stageInt;
                if(!int.TryParse(stageData.ToString(), out stageInt))
                {
                    throw new DataException("Could not load valid stage");
                }
                return (Stage) stageInt;
            }
        }

        public dynamic GetOrderEditView(Guid orderId, Stage stage)
        {
            switch (stage)
            {
                case Stage.OrderSummary:
                    return GetOrderSummary(orderId);
                case Stage.ContactInformation:
                    return GetContactInformation(orderId);
                case Stage.ComponentTitle:
                    return GetCakeTitle(orderId);
                case Stage.ComponentHeight:
                    return GetCakeHeight(orderId);
                case Stage.ComponentFlavour:
                    return GetFlavour(orderId);
                case Stage.ComponentQuantity:
                    return GetQuantity(orderId);
                case Stage.ComponentSize:
                    return GetComponentSizeModel(orderId);
                case Stage.ComponentButterCream:
                    return GetButtercream(orderId);
                case Stage.ComponentPreserve:
                    return GetPreserve(orderId);
                case Stage.ComponentFinishing:
                    return GetFinish(orderId);
                default:
                    throw new Exception("Invalid stage for order Id " + orderId);
            }

        }

        public dynamic GetSpecificOrderEditView(Guid stageId, Stage stage)
        {
            switch (stage)
            {
                case Stage.OrderSummary:
                    return GetSpecificOrderSummary(stageId);
                case Stage.ContactInformation:
                    return GetSpecificContactInformation(stageId);
                case Stage.ComponentTitle:
                    return GetSpecificCakeTitle(stageId);
                case Stage.ComponentHeight:
                    return GetSpecificCakeHeight(stageId);
                case Stage.ComponentFlavour:
                    return GetSpecificFlavour(stageId);
                case Stage.ComponentQuantity:
                    return GetSpecificQuantity(stageId);
                case Stage.ComponentSize:
                    return GetSpecificComponentSizeModel(stageId);
                case Stage.ComponentButterCream:
                    return GetSpecificButtercream(stageId);
                case Stage.ComponentPreserve:
                    return GetSpecificPreserve(stageId);
                case Stage.ComponentFinishing:
                    return GetSpecificFinish(stageId);
                default:
                    throw new Exception("Invalid stage for stage Id " + stageId);
            }
        }

        public ContactItem GetContact(Guid contactId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ContactItem>()
                                         .From(Db.Contact)
                                         .Map(x => x.County).To(Contact.County)
                                         .Map(x => x.Email).To(Contact.Email)
                                         .Map(x => x.Facebook).To(Contact.Facebook)
                                         .Map(x => x.FirstName).To(Contact.FirstName)
                                         .Map(x => x.SearchName).To(Contact.SearchName)
                                         .Map(x => x.HouseNameNumber).To(Contact.HouseNameNumber)
                                         .Map(x => x.Id).To(Contact.Id)
                                         .Map(x => x.LastName).To(Contact.LastName)
                                         .Map(x => x.PhoneNumber).To(Contact.PhoneNumber)
                                         .Map(x => x.PostCode).To(Contact.PostCode)
                                         .Map(x => x.Street).To(Contact.Street)
                                         .Map(x => x.Title).To(Contact.Title)
                                         .Map(x => x.Town).To(Contact.Town)
                                         .Where(new Filter<ContactItem>(Contact.Id).Is.EqualTo(contactId)));
            }
        }

        public List<TypeAHeadSearchResult> SearchContacts(string searchString)
        {
            using (var session = _sessionFactory.Create())
            {
                var result = session.Query(new Query<TypeAHeadSearchResult>()
                                               .From(Db.Contact)
                                               .Map(x => x.Id).To(Contact.Id)
                                               .Map(x => x.Value).To(Contact.SearchName)
                                               .Where(new Filter<TypeAHeadSearchResult>(Contact.SearchName)
                                                          .Is.Contains(searchString))
                                               .OrderBy(Contact.SearchName)
                                               .Top(10));
                return result.Results;
            }
        }

        public SelectItem GetCakeFlavourSelectItem(Guid flavourTypeId, bool isCarved)
        {
            var selectItem = new SelectItem("FlavourId", true);
            if (flavourTypeId == Guid.Empty)
            {
                selectItem.AddOption(new SelectOption("Please select a flavour type", null));
                return selectItem;
            }
            using (var session = _sessionFactory.Create())
            {
                var flavours = session.Query(new Query<CakeFlavourItem>()
                                                      .From(Db.CakeFlavour)
                                                      .Map(x => x.Id).To(CakeFlavour.Id)
                                                      .Map(x => x.Name).To(CakeFlavour.Name)
                                                      .Map(x => x.CanCarve).To(CakeFlavour.CanCarve)
                                                      .Where(new Filter<CakeFlavourItem>(CakeFlavour.TypeId).Is.EqualTo(flavourTypeId)));
                var selectableCount = 0;
                foreach (var flavour in flavours.Results)
                {
                    if (!flavour.CanCarve && isCarved)
                    {
                        selectItem.AddOption(new SelectOption(flavour.Name, flavour.Id, false, true, "Not suitable for carving."));
                    }
                    else
                    {
                        selectItem.AddOption(new SelectOption(flavour.Name, flavour.Id, false));
                        selectableCount++;
                    }
                }
                if (selectableCount == 1)
                {
                    selectItem.Options.Find(x => !x.IsDisabled).IsSelected = true;
                }
            }
            return selectItem;
        }

        public ShapeBuilderView GetShapeBuilderView(Guid componentId)
        {
            using(var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ShapeBuilderView>()
                                        .From(Db.Component)
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Map(x => x.OrderId).To(Component.OrderId)
                                        .Map(x => x.Portions).To(Component.Portions)
                                        .Map(x => x.ActiveCellString).To(Component.ShapeBuilderActiveCellString)
                                        .Where(new Filter<ShapeBuilderView>(Component.Id).Is.EqualTo(componentId)));
            }
        }

        public SelectItem GetPresetDecorations(Guid decorationTypeId)
        {
            var select = new SelectItem("PresetDecorations", true);
            using(var session = _sessionFactory.Create())
            {
                var data = session.Query(new Query<PresetDecoration>()
                                             .From(Db.Decoration)
                                             .MapAllProperties().To(Db.Decoration)
                                             .Where(
                                                 new Filter<PresetDecoration>(Decoration.DecorationTypeId).Is.EqualTo(
                                                     decorationTypeId)));
                foreach(var item in data.Results)
                {
                    var option = new SelectOption(item.Name, item.Id);
                    option.AddCustomAttribute("CanSetColour", item.CanSetColour.ToString(CultureInfo.InvariantCulture));
                    option.AddCustomAttribute("Description", item.Description);
                    select.AddOption(option);
                }
            }
            return select;
        }

        #endregion

        #region order edit view methods

        private OrderSummaryModel GetOrderSummary(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<OrderSummaryModel>()
                                        .From(Db.Order
                                                  .Join(new Join(Db.EntityStage)
                                                            .On(Order.CurrentLastStageId).IsEqualTo(EntityStage.Id)))
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.Title).To(Order.Title)
                                        .Map(x => x.Summary).To(Order.Summary)
                                        .Map(x => x.Due).To(Order.Due)
                                        .Map(x => x.ComponentId).To(EntityStage.ComponentId)
                                        .Where(new Filter<OrderSummaryModel>(Order.Id).Is.EqualTo(orderId)));
            }
        }

        private OrderSummaryModel GetSpecificOrderSummary(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<OrderSummaryModel>()
                                        .From(
                                            Db.EntityStage.Join(
                                                new Join(Db.Order).On(EntityStage.OrderId).IsEqualTo(Order.Id)))
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.Title).To(Order.Title)
                                        .Map(x => x.Summary).To(Order.Summary)
                                        .Map(x => x.Due).To(Order.Due)
                                        .Map(x => x.ComponentId).To(EntityStage.ComponentId)
                                        .Where(new Filter<OrderSummaryModel>(EntityStage.Id).Is.EqualTo(stageId)));
            }
        }

        private ContactInformationModel GetContactInformation(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ContactInformationModel>()
                                        .From(Db.Order
                                                  .Join(
                                                      new Join(Db.EntityStage).On(Order.CurrentLastStageId).IsEqualTo(
                                                          EntityStage.Id))
                                                  .OuterJoin(
                                                      new Join(Db.Contact).On(Order.ContactId).IsEqualTo(Contact.Id)))
                                        .Map(x => x.Title).To(Contact.Title)
                                        .Map(x => x.FirstName).To(Contact.FirstName)
                                        .Map(x => x.LastName).To(Contact.LastName)
                                        .Map(x => x.Email).To(Contact.Email)
                                        .Map(x => x.Facebook).To(Contact.Facebook)
                                        .Map(x => x.PhoneNumber).To(Contact.PhoneNumber)
                                        .Map(x => x.HouseNameNumber).To(Contact.HouseNameNumber)
                                        .Map(x => x.Street).To(Contact.Street)
                                        .Map(x => x.Town).To(Contact.Town)
                                        .Map(x => x.County).To(Contact.County)
                                        .Map(x => x.PostCode).To(Contact.PostCode)
                                        .Map(x => x.OrderId).To(EntityStage.OrderId)
                                        .Map(x => x.ComponentId).To(EntityStage.ComponentId)
                                        .Where(new Filter<ContactInformationModel>(Order.Id).Is.EqualTo(orderId)));
            }
        }

        private ContactInformationModel GetSpecificContactInformation(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ContactInformationModel>()
                                        .From(Db.EntityStage
                                                  .Join(new Join(Db.Order).On(EntityStage.OrderId).IsEqualTo(Order.Id)
                                                            .SubOuterJoin(
                                                                new Join(Db.Contact).On(Order.ContactId).IsEqualTo(
                                                                    Contact.Id))))
                                        .Map(x => x.Title).To(Contact.Title)
                                        .Map(x => x.FirstName).To(Contact.FirstName)
                                        .Map(x => x.LastName).To(Contact.LastName)
                                        .Map(x => x.Email).To(Contact.Email)
                                        .Map(x => x.Facebook).To(Contact.Facebook)
                                        .Map(x => x.PhoneNumber).To(Contact.PhoneNumber)
                                        .Map(x => x.HouseNameNumber).To(Contact.HouseNameNumber)
                                        .Map(x => x.Street).To(Contact.Street)
                                        .Map(x => x.Town).To(Contact.Town)
                                        .Map(x => x.County).To(Contact.County)
                                        .Map(x => x.PostCode).To(Contact.PostCode)
                                        .Map(x => x.OrderId).To(EntityStage.OrderId)
                                        .Map(x => x.ComponentId).To(EntityStage.ComponentId)
                                        .Where(new Filter<ContactInformationModel>(EntityStage.Id).Is.EqualTo(stageId)));
            }
        }

        private CakeTitleModel GetCakeTitle(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<CakeTitleModel>()
                                        .From(Db.Order
                                                  .Join(new Join(Db.Component)
                                                            .On(Order.CurrentLastComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Map(x => x.Title).To(Component.Title)
                                        .Map(x => x.Type).To(Component.ComponentType)
                                        .Map(x => x.IsCarved).To(Component.IsCarved)
                                        .Where(new Filter<CakeTitleModel>(Order.Id).Is.EqualTo(orderId)));
            }
        }

        private CakeTitleModel GetSpecificCakeTitle(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<CakeTitleModel>()
                                        .From(Db.EntityStage
                                                  .Join(new Join(Db.Component)
                                                            .On(EntityStage.ComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.OrderId).To(EntityStage.Id)
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Map(x => x.Title).To(Component.Title)
                                        .Map(x => x.Type).To(Component.ComponentType)
                                        .Map(x => x.IsCarved).To(Component.IsCarved)
                                        .Where(new Filter<CakeTitleModel>(EntityStage.Id).Is.EqualTo(stageId)));
            }
        }

        private ComponentHeightModel GetCakeHeight(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ComponentHeightModel>()
                                        .From(Db.Order
                                                  .Join(new Join(Db.Component)
                                                            .On(Order.CurrentLastComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.Height).To(Component.Height)
                                        .Map(x => x.HeightType).To(Component.HeightType)
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Where(new Filter<ComponentHeightModel>(Order.Id).Is.EqualTo(orderId)));
            }
        }

        private ComponentHeightModel GetSpecificCakeHeight(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ComponentHeightModel>()
                                        .From(Db.EntityStage
                                                  .Join(new Join(Db.Component)
                                                            .On(EntityStage.ComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.Height).To(Component.Height)
                                        .Map(x => x.HeightType).To(Component.HeightType)
                                        .Map(x => x.OrderId).To(EntityStage.OrderId)
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Where(new Filter<ComponentHeightModel>(EntityStage.Id).Is.EqualTo(stageId)));
            }
        }

        private ComponentFlavourModel GetFlavour(Guid orderId)
        {
            using(var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentFlavourModel>()
                                             .From(Db.Order
                                                       .Join(new Join(Db.Component)
                                                                 .On(Order.CurrentLastComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Order.Id)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.ComponentType).To(Component.ComponentType)
                                             .Map(x => x.IsCarved).To(Component.IsCarved)
                                             .Map(x => x.NumberOfFlavours).To(Component.NumberOfFlavours)
                                             .Where(new Filter<ComponentFlavourModel>(Order.Id).Is.EqualTo(orderId)));
                return SetFlavours(session, model);
            }
        }

        private ComponentFlavourModel SetFlavours(IDbSession session, ComponentFlavourModel model)
        {
            var flavourTypes = session.Query(new Query<CakeFlavourTypeItem>()
                                                     .From(Db.CakeFlavourType)
                                                     .Map(x => x.Id).To(CakeFlavourType.Id)
                                                     .Map(x => x.Name).To(CakeFlavourType.Name)
                                                     .Map(x => x.CanCarve).To(CakeFlavourType.CanCarve)
                                                     .Map(x => x.IsCompatibleWith3DCakes)
                                                     .To(CakeFlavourType.IsCompatibleWith3DCakes)
                                                     .Map(x => x.IsCompatibleWithCupCakes)
                                                     .To(CakeFlavourType.IsCompatibleWithCupCakes)
                                                     .Map(x => x.IsCompatibleWithGiantCakes)
                                                     .To(CakeFlavourType.IsCompatibleWithGiantCakes)
                                                     .OrderBy(CakeFlavourType.Name));
            model.AddFlavourTypes(flavourTypes.Results);

            var currentFlavours = session.Query(new Query<ComponentFlavourModelItem>()
                                                    .From(Db.ComponentFlavour
                                                              .Join(new Join(Db.CakeFlavour)
                                                                        .On(ComponentFlavour.CakeFlavourId)
                                                                        .IsEqualTo(CakeFlavour.Id))
                                                              .Join(new Join(Db.CakeFlavourType)
                                                                        .On(ComponentFlavour.CakeFlavourTypeId)
                                                                        .IsEqualTo(CakeFlavourType.Id)))
                                                    .Map(x => x.FlavourId).To(ComponentFlavour.CakeFlavourId)
                                                    .Map(x => x.FlavourName).To(CakeFlavour.Name)
                                                    .Map(x => x.FlavourTypeName).To(CakeFlavourType.Name)
                                                    .Where(
                                                        new Filter<ComponentFlavourModelItem>(
                                                            ComponentFlavour.ComponentId).Is.EqualTo(
                                                                model.ComponentId)));
            model.Flavours = currentFlavours.Results;
            return model;
        }

        private ComponentFlavourModel GetSpecificFlavour(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentFlavourModel>()
                                             .From(Db.EntityStage
                                                       .Join(new Join(Db.Component)
                                                                 .On(EntityStage.ComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(EntityStage.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.ComponentType).To(Component.ComponentType)
                                             .Map(x => x.IsCarved).To(Component.IsCarved)
                                             .Map(x => x.NumberOfFlavours).To(Component.NumberOfFlavours)
                                             .Where(new Filter<ComponentFlavourModel>(EntityStage.Id).Is.EqualTo(stageId)));

                return SetFlavours(session, model);
            }
        }

        private ComponentSizeModel GetComponentSizeModel(Guid orderId)
        {
            using(var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentSizeModel>()
                                             .From(Db.Order
                                                       .Join(new Join(Db.Component)
                                                                 .On(Order.CurrentLastComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.IsCarved).To(Component.IsCarved)
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.TotalPortions).To(Component.Portions)
                                             .Where(new Filter<ComponentSizeModel>(Order.Id).Is.EqualTo(orderId)));
                return SetSizes(session, model);
            }
        }

        private ComponentSizeModel SetSizes(IDbSession session, ComponentSizeModel model)
        {
            var sizes = session.Query(new Query<ComponentSizeModelItem>()
                                             .From(Db.ComponentSize)
                                             .Map(x => x.CustomDetail).To(ComponentSize.CustomShapeDetail)
                                             .Map(x => x.Portions).To(ComponentSize.Portions)
                                             .Map(x => x.Shape).To(ComponentSize.Shape)
                                             .Map(x => x.Size).To(ComponentSize.Size)
                                             .Map(x => x.SizeId).To(ComponentSize.Id)
                                             .Map(x => x.Width).To(ComponentSize.Width)
                                             .Where(
                                                 new Filter<ComponentSizeModelItem>(ComponentSize.ComponentId).Is.
                                                     EqualTo(model.ComponentId)));

            model.AddSizes(sizes.Results);
            return model;
        }

        private ComponentSizeModel GetSpecificComponentSizeModel(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentSizeModel>()
                                             .From(Db.EntityStage
                                                       .Join(new Join(Db.Component)
                                                                 .On(EntityStage.ComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.IsCarved).To(Component.IsCarved)
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.TotalPortions).To(Component.Portions)
                                             .Where(new Filter<ComponentSizeModel>(EntityStage.Id).Is.EqualTo(stageId)));

                return SetSizes(session, model);
            }
        }
        
        private ComponentQuantityModel GetQuantity(Guid orderId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ComponentQuantityModel>()
                                        .From(Db.Order
                                                  .Join(new Join(Db.Component)
                                                            .On(Order.CurrentLastComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.Quantity).To(Component.Quantity)
                                        .Where(new Filter<ComponentQuantityModel>(Order.Id).Is.EqualTo(orderId)));
            }
        }

        private ComponentQuantityModel GetSpecificQuantity(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                return session.Load(new Query<ComponentQuantityModel>()
                                        .From(Db.EntityStage
                                                  .Join(new Join(Db.Component)
                                                            .On(EntityStage.ComponentId)
                                                            .IsEqualTo(Component.Id)))
                                        .Map(x => x.ComponentId).To(Component.Id)
                                        .Map(x => x.OrderId).To(Order.Id)
                                        .Map(x => x.Quantity).To(Component.Quantity)
                                        .Where(new Filter<ComponentQuantityModel>(EntityStage.Id).Is.EqualTo(stageId)));
            }
        }

        private ComponentButterCreamModel GetButtercream(Guid orderId)
        {
            using(var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentButterCreamModel>()
                                             .From(Db.Order
                                                       .Join(new Join(Db.Component)
                                                                 .On(Order.CurrentLastComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.ButterCreamId).To(Component.ButtercreamId)
                                             .Map(x => x.GanacheId).To(Component.GanacheId)
                                             .Map(x => x.ButterCreamColour).To(Component.FillingColour)
                                             .Where(new Filter<ComponentButterCreamModel>(Order.Id).Is.EqualTo(orderId)));
                return SetButterCreamFillings(session, model);
            }
        }

        private ComponentButterCreamModel SetButterCreamFillings(IDbSession session, ComponentButterCreamModel model)
        {
            var fillings = session.Query(new Query<FillingItem>()
                                                .From(Db.FillingFlavour
                                                          .Join(new Join(Db.FillingType)
                                                                    .On(FillingFlavour.FillingTypeId)
                                                                    .IsEqualTo(FillingType.Id)))
                                                .Map(x => x.Id).To(FillingFlavour.Id)
                                                .Map(x => x.Name).To(FillingFlavour.Name)
                                                .Map(x => x.FillingTypeId).To(FillingType.Id)
                                                .Map(x => x.FillingTypeName).To(FillingType.Name)
                                                .OrderBy(FillingFlavour.Name)
                                                .Where(new Filter<FillingItem>(FillingFlavour.Name).Is.EqualTo(Constants.ButterCream)
                                                           .Or(FillingFlavour.Name).Is.EqualTo(Constants.Ganache)));
            var incompatibleFillings = session.Query(new Query<IncompatibleItem>()
                                                         .From(Db.ComponentFlavour
                                                                   .Join(new Join(Db.IncompatibleFillingForCake)
                                                                             .On(ComponentFlavour.CakeFlavourId)
                                                                             .IsEqualTo(IncompatibleFillingForCake.CakeFlavourId)))
                                                         .Map(x => x.Id).To(IncompatibleFillingForCake.FillingFlavourId)
                                                         .Map(x => x.IncompatibleWithId).To(IncompatibleFillingForCake.CakeFlavourId)
                                                         .Where(new Filter<IncompatibleItem>(ComponentFlavour.ComponentId).Is.EqualTo(model.ComponentId)));
            model.AddFillings(fillings.Results, incompatibleFillings.Results);
            return model;
        }

        private ComponentButterCreamModel GetSpecificButtercream(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentButterCreamModel>()
                                             .From(Db.EntityStage
                                                       .Join(new Join(Db.Component)
                                                                 .On(EntityStage.ComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.ButterCreamId).To(Component.ButtercreamId)
                                             .Map(x => x.GanacheId).To(Component.GanacheId)
                                             .Map(x => x.ButterCreamColour).To(Component.FillingColour)
                                             .Where(new Filter<ComponentButterCreamModel>(EntityStage.Id).Is.EqualTo(stageId)));
                return SetButterCreamFillings(session, model);
            }
        }

        private ComponentPreserveModel GetPreserve(Guid orderId)
        {
            using(var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentPreserveModel>()
                                             .From(Db.Order
                                                       .Join(new Join(Db.Component)
                                                                 .On(Order.CurrentLastComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.PreserveId).To(Component.PreserveId)
                                             .Map(x => x.ButtercreamId).To(Component.ButtercreamId)
                                             .Map(x => x.GanacheId).To(Component.GanacheId)
                                             .Where(new Filter<ComponentPreserveModel>(Order.Id).Is.EqualTo(orderId)));
                return SetPreserveItems(session, model);
            }
        }

        private ComponentPreserveModel SetPreserveItems(IDbSession session, ComponentPreserveModel model)
        {
            var fillings = session.Query(new Query<FillingItem>()
                                                .From(Db.FillingFlavour
                                                          .Join(new Join(Db.FillingType)
                                                                    .On(FillingFlavour.FillingTypeId)
                                                                    .IsEqualTo(FillingType.Id)))
                                                .Map(x => x.Id).To(FillingFlavour.Id)
                                                .Map(x => x.Name).To(FillingFlavour.Name)
                                                .Map(x => x.FillingTypeId).To(FillingType.Id)
                                                .Map(x => x.FillingTypeName).To(FillingType.Name)
                                                .OrderBy(FillingFlavour.Name)
                                                .Where(new Filter<FillingItem>(FillingType.Name).Is.EqualTo(Constants.Preserve)));
            var incompatibleWithFilling = new List<IncompatibleItem>();
            if(model.ButtercreamId.HasValue)
            {
                incompatibleWithFilling.AddRange(session.Query(new Query<IncompatibleItem>()
                                                                   .From(Db.IncompatibleFillingForFilling)
                                                                   .Map(x => x.Id).To(IncompatibleFillingForFilling.FillingFlavour)
                                                                   .Map(x => x.IncompatibleWithId).To(IncompatibleFillingForFilling.BaseFillingFlavour)
                                                                   .Where(new Filter<IncompatibleItem>(IncompatibleFillingForFilling.BaseFillingFlavour).Is.EqualTo(model.ButtercreamId.Value)))
                                                                   .Results);
            }
            if(model.GanacheId.HasValue)
            {
                incompatibleWithFilling.AddRange(session.Query(new Query<IncompatibleItem>()
                                                                   .From(Db.IncompatibleFillingForFilling)
                                                                   .Map(x => x.Id).To(IncompatibleFillingForFilling.FillingFlavour)
                                                                   .Map(x => x.IncompatibleWithId).To(IncompatibleFillingForFilling.BaseFillingFlavour)
                                                                   .Where(new Filter<IncompatibleItem>(IncompatibleFillingForFilling.BaseFillingFlavour).Is.EqualTo(model.GanacheId.Value)))
                                                                   .Results);
            }

            var incompatibleWithCake = session.Query(new Query<IncompatibleItem>()
                                                         .From(Db.ComponentFlavour
                                                                   .Join(new Join(Db.IncompatibleFillingForCake)
                                                                             .On(ComponentFlavour.CakeFlavourId)
                                                                             .IsEqualTo(IncompatibleFillingForCake.CakeFlavourId)))
                                                         .Map(x => x.Id).To(IncompatibleFillingForCake.FillingFlavourId)
                                                         .Map(x => x.IncompatibleWithId).To(IncompatibleFillingForCake.CakeFlavourId)
                                                         .Where(new Filter<IncompatibleItem>(ComponentFlavour.ComponentId).Is.EqualTo(model.ComponentId)));

            model.AddPreserves(fillings.Results, incompatibleWithFilling, incompatibleWithCake.Results);
            return model;

        }

        private ComponentPreserveModel GetSpecificPreserve(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentPreserveModel>()
                                             .From(Db.EntityStage
                                                       .Join(new Join(Db.Component)
                                                                 .On(EntityStage.ComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.PreserveId).To(Component.PreserveId)
                                             .Map(x => x.ButtercreamId).To(Component.ButtercreamId)
                                             .Map(x => x.GanacheId).To(Component.GanacheId)
                                             .Where(new Filter<ComponentPreserveModel>(EntityStage.Id).Is.EqualTo(stageId)));
                return SetPreserveItems(session, model);
            }
        }

        private ComponentFinishingModel GetFinish(Guid orderId)
        {
            using(var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentFinishingModel>()
                                             .From(Db.Order
                                                       .Join(new Join(Db.Component)
                                                                 .On(Order.CurrentLastComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.FinishColour).To(Component.CoveringColour)
                                             .Map(x => x.CoveringType).To(Component.CoveringType)
                                             .Map(x => x.FinishType).To(Component.FinishType)
                                             .Where(new Filter<ComponentFinishingModel>(Order.Id).Is.EqualTo(orderId)));

                return GetFinishIsFruit(session, model);
            }
        }

        private ComponentFinishingModel GetFinishIsFruit(IDbSession session, ComponentFinishingModel model)
        {
            var cakeFlavour = session.Load(new Query()
                                               .From(Db.ComponentFlavour)
                                               .Select(ComponentFlavour.Id)
                                               .Where(
                                                   new Filter(ComponentFlavour.ComponentId).Is.EqualTo(model.ComponentId)));
            if(cakeFlavour == null || string.IsNullOrEmpty(cakeFlavour.Value("Id").ToString()))
                return model;

            model.IsFruit = true;
            return model;
        }

        private ComponentFinishingModel GetSpecificFinish(Guid stageId)
        {
            using (var session = _sessionFactory.Create())
            {
                var model = session.Load(new Query<ComponentFinishingModel>()
                                             .From(Db.EntityStage
                                                       .Join(new Join(Db.Component)
                                                                 .On(EntityStage.ComponentId)
                                                                 .IsEqualTo(Component.Id)))
                                             .Map(x => x.OrderId).To(Component.OrderId)
                                             .Map(x => x.ComponentId).To(Component.Id)
                                             .Map(x => x.FinishColour).To(Component.CoveringColour)
                                             .Map(x => x.CoveringType).To(Component.CoveringType)
                                             .Map(x => x.FinishType).To(Component.FinishType)
                                             .Where(
                                                 new Filter<ComponentFinishingModel>(EntityStage.Id).Is.EqualTo(stageId)));

                return GetFinishIsFruit(session, model);
            }
        }

        #endregion
    }
}
